using System;
using System.Data;
using System.Text;

using Elev.Core.Data;

namespace Elev.Core.DAL
{
    internal class StudentInternal : SqliteTable
    {
        void ensurePascalCasing(ref string input)
        {
            if (input.Length > 0)
            {
                input = input.Trim().ToLower();
                input = input.Substring(0, 1).ToUpper() + input.Substring(1);
            }
        }

        bool Exists(string FirstName, string LastName)
        {
            return Exists(FirstName, LastName, -1);
        }

        bool Exists(string FirstName, string LastName, int NotIn)
        {
            StringBuilder SQL = new StringBuilder("SELECT COUNT(*) FROM Student WHERE FirstName = @FirstName AND LastName = @LastName");

            if (NotIn != -1)
            {
                SQL.AppendFormat(" AND ID != {0}", NotIn);
            }
            
            return (Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar(SQL.ToString(), "@FirstName", FirstName, "@LastName", LastName)) > 0);
        }

        /// <summary>
        /// Returns a DataTable containing all the student in the database
        /// </summary>
        internal DataTable GetStudents()
        {
            string SQL = "SELECT * FROM Student ORDER BY LastName, FirstName";
            return SqliteAdapter.Get().Execute(SQL);
        }

        /// <summary>
        /// Returns a DataTable containing one given student
        /// </summary>
        internal DataTable GetStudent(int ID)
        {
            string SQL = "SELECT * FROM Student WHERE ID = @ID";
            return SqliteAdapter.Get().Execute(SQL, "@ID", ID);
        }

        /// <summary>
        /// Inserts a new Student in the database and returns the ID of this new student
        /// </summary>
        internal int Add(string FirstName, string LastName, string Note)
        {            
            if (FirstName == "" || LastName == "")
                throw new ArgumentException("To add a student, a first name and a last name are required.");

            ensurePascalCasing(ref FirstName);
            ensurePascalCasing(ref LastName);

            if (Exists(FirstName, LastName))
                throw new ArgumentException("A student with the same first name and last name already exists.");

            string SQL = "INSERT INTO Student(FirstName, LastName, Note) VALUES(@FirstName, @LastName, @Note)";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@FirstName", FirstName, "@LastName", LastName, "@Note", Note);

            return Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar("SELECT MAX(ID) FROM Student"));
        }

        internal void Update(int ID, string FirstName, string LastName, string Note)
        {
            if (FirstName == "" || LastName == "")
                throw new ArgumentException("To update a student, a first name and a last name are required.");

            ensurePascalCasing(ref FirstName);
            ensurePascalCasing(ref LastName);

            if (Exists(FirstName, LastName, ID))
                throw new ArgumentException("A student with the same first name and last name already exists.");
            
            string SQL = "UPDATE Student SET FirstName = @FirstName, LastName = @LastName, Note = @Note WHERE ID = @ID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@FirstName", FirstName, "@LastName", LastName, "@Note", Note, "@ID", ID);
        }

        internal void Delete(int ID)
        {
            string SQL = "DELETE FROM Student WHERE ID = @ID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ID", ID);

            // Delete all the grades that belonged to this student
            SQL = "DELETE FROM Grade WHERE StudentID = @StudentID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@StudentID", ID);
        }

        #region SqliteTable Members

        public override void Create()
        {
            string SQL = "CREATE TABLE [Student] (" +
                            " ID INTEGER DEFAULT '' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE," +
                            " FirstName VARCHAR(100) DEFAULT '' NOT NULL," +
                            " LastName VARCHAR(150) DEFAULT '' NOT NULL," +
                            " Note TEXT)";

            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        public override void Drop()
        {
            string SQL = "DROP TABLE [Student]";
            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        #endregion
    }
}
